package com.eling.elcms.travel.dao.hibernate;

import com.eling.elcms.basedoc.model.PersonalInfo;
import com.eling.elcms.core.dao.hibernate.GenericDaoHibernate;
import com.eling.elcms.ordermanager.model.ServicePoint;
import com.eling.elcms.travel.dao.ICarDispatchDao;
import com.eling.elcms.travel.model.*;
import com.eling.elcms.travel.model.CarDispatchBill.CarStatus;
import org.hibernate.SQLQuery;
import org.hibernate.transform.Transformers;
import org.springframework.stereotype.Repository;

import java.util.ArrayList;
import java.util.Date;
import java.util.List;
import java.util.Map;

@Repository
public class CarDispatchDaoHibernate extends GenericDaoHibernate<CarDispatchBill, Long> implements ICarDispatchDao{

	public CarDispatchDaoHibernate() {
		super(CarDispatchBill.class);
	}

	@SuppressWarnings({ "unchecked", "rawtypes" })
	@Override
	public List<CarDispatchBill> queryAllRecord(CarDispatchBill cond) {
		StringBuffer sql = new StringBuffer();
		sql.append(" SELECT bcd.pkCarDispatchBill,bcd.oddCode,bcd.unitsCar,bcd.companyName,bcd.carModel, bcd.totalPrice, ");
		sql.append(" bcd.driverName,bcd.driverPhone,bcd.dispatchDate,bcd.backDate, bd.pkDriver,                        ");
		sql.append(" bcd.address,bcd.description,bcd.version,bcd.carStatus,bc.carType,bc.carNumber,bc.pkCar,bpi.`name` name1,   ");
		sql.append(" bpi.phone,tpg.pkPlanItemGroup,tao.`name` name2,os.`name` name3,tnmg.contactName,                    ");
		sql.append(" tnmg.checkInDate,tnmg.checkOutDate,tnmg.perNumber,os2.`name` name4                                     ");
		sql.append("                                                                                                     ");
		sql.append("  FROM bd_cardispatch bcd                                                                            ");
		sql.append(" LEFT  JOIN bd_car bc ON bc.pkCar = bcd.pkCar                                                        ");
		sql.append(" LEFT  JOIN bd_driver bd ON bd.pkDriver = bcd.pkDriver                                               ");
		sql.append(" LEFT  JOIN bd_personalinfo bpi ON bpi.pkPersonalInfo = bd.pkPersonalInfo                            ");
		sql.append("                                                                                                     ");
		sql.append(" LEFT  JOIN travel_groupreserve tgr ON tgr.pkGroupReserve = bcd.pkGroupReserve                       ");
		sql.append(" LEFT  JOIN travel_planitemgroup tpg ON tpg.pkPlanItemGroup = tgr.pkPlanItemGroup                    ");
		sql.append(" LEFT  JOIN travel_allianceorg tao ON tao.pkAllianceOrg = tpg.pkAllianceOrg                          ");
		sql.append(" LEFT  JOIN om_servicepoint os ON os.pkServicePoint = tpg.pkServicePoint                             ");
		sql.append("                                                                                                     ");
		sql.append(" LEFT JOIN travel_nonmemgroupreserve tnmgr ON tnmgr.pkNonMemGroupReserve = bcd.pkNonMemGroupReserve  ");
		sql.append(" LEFT JOIN travel_nonmembergroup tnmg ON tnmg.pkNonMemberGroup = tnmgr.pkNonMemberGroup              ");
		sql.append(" LEFT JOIN om_servicepoint os2 on os2.pkServicePoint = tnmg.pkServicePoint                           ");
		if(cond.getCarStatus() != null){
			sql.append(" WHERE bcd.carStatus = "+ cond.getCarStatus() +"                                                 ");
		}
		sql.append("                                                                                                     ");
		sql.append(" ORDER BY bcd.dispatchDate DESC                                                                      ");
		
//		List<Object[]> objList = getSession().createNativeQuery(sql.toString()).list();
		
		SQLQuery query = getSession().createNativeQuery(sql.toString());
		query.setResultTransformer(Transformers.ALIAS_TO_ENTITY_MAP); 
		List objList = query.list();
		
		List<CarDispatchBill> carList = new ArrayList<>();
		for(int i = 0; i < objList.size(); i++){
			Map map = (Map) objList.get(i);
			ServicePoint point = new ServicePoint();//散客服务点
			point.setName(map.get("name4") == null?"":map.get("name4").toString());
			NonMemberGroup nonMemberGroup = new NonMemberGroup();
			nonMemberGroup.setPerNumber(map.get("perNumber") == null?0.0:Double.parseDouble(map.get("perNumber").toString()));
			nonMemberGroup.setCheckOutDate(map.get("checkOutDate") == null?null:(Date)map.get("checkOutDate"));
			nonMemberGroup.setCheckInDate(map.get("checkInDate") == null?null:(Date)map.get("checkInDate"));
			nonMemberGroup.setContactName(map.get("contactName") == null?"":map.get("contactName").toString());
			nonMemberGroup.setServicePoint(point);
			NonMemGroupReserve nonMemGroupReserve = new NonMemGroupReserve();
			nonMemGroupReserve.setNonMemberGroup(nonMemberGroup);
			PlanItemGroup planItemGroup = new PlanItemGroup();
			if(map.get("name3") != null){
				ServicePoint servicePoint = new ServicePoint();
				servicePoint.setName(map.get("name3").toString());
				planItemGroup.setServicePoint(servicePoint);
			}
			if(map.get("name2") != null){
				AllianceOrg allianceOrg = new AllianceOrg();
				allianceOrg.setName(map.get("name2").toString());
				planItemGroup.setAllianceOrg(allianceOrg);
			}
			planItemGroup.setPkPlanItemGroup(map.get("pkPlanItemGroup") == null ? 0:Long.parseLong(map.get("pkPlanItemGroup").toString()));
			GroupReserve groupReserve = new GroupReserve();
			groupReserve.setPlanItemGroup(planItemGroup);
			PersonalInfo personalInfo = new PersonalInfo();
			personalInfo.setPhone(map.get("phone") == null?"":map.get("phone").toString());
			personalInfo.setName(map.get("name1") == null ?"":map.get("name1").toString());
			Driver driver = new Driver();
			driver.setPersonalInfo(personalInfo);
			driver.setPkDriver(map.get("pkDriver") == null ? 0:Long.parseLong(map.get("pkDriver").toString()));
			Car car = new Car();
			car.setPkCar(map.get("pkCar") == null ? 0:Long.parseLong(map.get("pkCar").toString()));
			car.setCarNumber(map.get("carNumber") == null ?"":map.get("carNumber").toString());
			car.setCarType(map.get("carType") == null ?"":map.get("carType").toString());
			CarDispatchBill carDispatchBill = new CarDispatchBill();
			carDispatchBill.setCarStatus(CarStatus.valueOf(map.get("carStatus").toString()));
			carDispatchBill.setVersion(map.get("version") == null ?0:Integer.parseInt(map.get("version").toString()));
			carDispatchBill.setDescription(map.get("description") == null ?"":map.get("description").toString());
			carDispatchBill.setAddress(map.get("address") == null ?"":map.get("address").toString());
			carDispatchBill.setBackDate(map.get("backDate") == null?null:(Date)map.get("backDate"));
			carDispatchBill.setDispatchDate(map.get("dispatchDate") == null?null:(Date)map.get("dispatchDate"));
			carDispatchBill.setDriverPhone(map.get("driverPhone") == null ?"":map.get("driverPhone").toString());
			carDispatchBill.setDriverName(map.get("driverName") == null ?"":map.get("driverName").toString());
			carDispatchBill.setCarModel(map.get("carModel") == null ?"":map.get("carModel").toString());
			carDispatchBill.setCompanyName(map.get("companyName") == null ?"":map.get("companyName").toString());
			carDispatchBill.setUnitsCar(map.get("unitsCar") == null ? false:Boolean.parseBoolean(map.get("unitsCar").toString()));
			carDispatchBill.setOddCode(map.get("oddCode") == null ?"":map.get("oddCode").toString());
			carDispatchBill.setTotalPrice(map.get("totalPrice") == null ? null:Double.parseDouble(map.get("totalPrice").toString()));
			carDispatchBill.setPkCarDispatchBill(map.get("pkCarDispatchBill") == null ? 0:Long.parseLong(map.get("pkCarDispatchBill").toString()));
			carDispatchBill.setGroupReserve(groupReserve);
			carDispatchBill.setNonMemGroupReserve(nonMemGroupReserve);
			if(map.get("name3") == null && map.get("name2") == null){
				carDispatchBill.setGroupReserve(null);
			}else{
				carDispatchBill.setNonMemGroupReserve(null);
			}
			
			carDispatchBill.setCar(car);
			carDispatchBill.setDriver(driver);
			carList.add(carDispatchBill);
		}
		return carList;
	}
}
